
package cassandrawindow;

/**
 * @author ADMIN
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.sql.Time;
import java.util.*;
import java.util.ArrayList;

public class MysqlDataManager {

    private Connection conn = null;
    
    private String TABLE_BANNERS        = "ox_banners";
    private String TABLE_CAMPAIGN       = "ox_campaigns";
    
    private String V3_BAN_DATE          = "mob_ban_date";
    private String V3_BAN_HOURLY        = "mob_ban_hourly";

    private String V3_CAM_DATE          = "mob_cam_date";
    private String V3_CAM_HOURLY        = "mob_cam_hourly";
    private String V3_CAM_HOURLY_CPC    = "mob_cam_hourly";
    
    /*for mobile*/
    private String TEST_MOB_BAN_DATE            = "mob_ban_date";
    private String TEST_MOB_BAN_HOURLY          = "mob_ban_hourly";
    private String TEST_MOB_CAM_DATE            = "mob_cam_date";
    private String TEST_MOB_CAM_HOURLY          = "mob_cam_hourly";
    //private String TEST_MOB_CAM_HOURLY_CPC      = "mob_cam_hourly";
    private String TEST_MOB_DOMAIN_DATE         = "mob_domain_date";
    /*end for mobile*/
    
    
    private String V3_CAM_IP        = "v3_cam_ip";
    
    private String V3_DOMAIN_DATE   = "mob_domain_date";
    private String V3_ZONE_DATE     = "v3_zone_date";

    private String FRAUD_CLICK      = "fraud_click";
    private String BAN_PRO_TABLE    = "banlance_promotion";
    
    private String V3_BAN_HOURLY_TEST    = "v3_ban_hourly";
    private String V3_CAM_HOURLY_TEST    = "v3_cam_hourly";

    private String V3_BANNER_LOG    = "domain_banner_log";

    public MysqlDataManager() throws ClassNotFoundException, InstantiationException, IllegalAccessException
    {
        /*try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();            
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL , ACCOUNT.username_mysql, ACCOUNT.pass_mysql);//abc!@#           
            //Debug.println("Connected to MySQL Server database");
        }
        catch (Exception ex)
        {
            Debug.println("Couldn't connect to the MySQL database 23.86.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
        }*/
         try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();            
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_MAIN_67 , ACCOUNT.username_mysql_main_67, ACCOUNT.pass_mysql_main_67);//abc!@#
            Debug.println("Connected to MySQL Server 6.7 database");
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            Debug.println("Couldn't connect to the MySQL database 6.7.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }
    }

    public void shutdownConnection()
    {        
        try {
            conn.close();
            conn = null;  
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
        
    public List getAllCampaignId()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select campaignid from "+ TABLE_BANNERS);
            while (rs.next())
            {
                infor.add(rs.getString("campaignid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return infor;
    }

    public List getBannerIDByCampaignId(int campaignId)
    {
        List infor = new ArrayList();        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ TABLE_BANNERS +" where campaignid = "+campaignId+"");
            while (rs.next())
            {
                infor.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return infor;

    }

    public void insertDataBannerHour(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_HOURLY_TEST +"(bannerid,clickhour,viewhour,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);            
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }
    public void insertDataCampaignDay(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_DATE +"(campaignid,totalclick,totalview,realclick,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
    }

    public void insertDataCampainHour(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_CPC +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
    }

    public void insertDataCampaignAndBanner(String campaignid,String bannerid,String totalclick,String totalview, String dt)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +" values (" + Integer.valueOf(campaignid) + ", " + Integer.valueOf(bannerid) +"," + Integer.valueOf(totalclick) +",0,0," + Integer.valueOf(totalview) +",0,0,0,0,0,0,0,'" + dt + "',0)";
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

    }

    public void insertSliceDataCampaignAndBanner(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +"(campaignid,bannerid,totalclick,realclick,clickcharge,totalview,viewcharge,uc,uv,MB,MT,MN,Other,dt,money) values " + value;
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }

    public void insertDataCampaignIp(String value){
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_IP +"(campaignid, ip, dt) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
    }

    public void insertDataDomainData(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE +"(campaignid, bannerid, domain, totalclick, realclick, totalview, viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

    }

    public void insertDataDomainDataBannerLog(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BANNER_LOG +"(bannerid, hang_domain, log_domain, hang_click, log_click, hang_view, log_view, dt) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

    }

    public void insertDataZone(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE +"(campaignid, zoneid, bannerid, totalclick, realclick, clickcharge, totalview, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

    }

    public boolean checkExitsLineFraudClick(String line)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select * from "+ FRAUD_CLICK +" where line = "+line+"");
            if (rs.next())
            {
                retVal = true;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return retVal;
    }

    public void insertFraudClick(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "insert ignore into "+ FRAUD_CLICK +"(line, guidid, zoneid, campaingnid, bannerid, domain, status, created_date) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }

    public int getCountFraudClickByBannerIDAndDay(int bannerId, String Day)
    {
        int retValue = 0;
             
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = " + bannerId + " AND created_date = '"+ Day +"' AND status = 0");            

            while (rs.next())
            {
                retValue = rs.getInt(1);                
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
            
        return retValue;
    }

    public boolean updateFraudClick(int bannerId, String Day)
    {
        boolean check = false;
        int fraudClickCount = getCountFraudClickByBannerIDAndDay(bannerId, Day);
        Debug.printlnFaudClick("So fraukClick với bannerid "+ bannerId +" ngay " +Day +":"+fraudClickCount);
        //fraudClickCount = 100;
        try {
            Statement s = conn.createStatement();
            String str1 = "realclick = if((totalclick - "+ fraudClickCount+" ) > 0,totalclick - "+ fraudClickCount +" ,0)";
            String sql = "update  "+ V3_BAN_DATE +" set "+ str1 +" where bannerid = " + bannerId + " AND dt = '"+ Day +"'" ;
            check = s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return check;
    }

    public List getValidBanner()
    {
        List addInfo = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ V3_BAN_DATE +" where totalclick  > 0");
            while (rs.next())
            {
                addInfo.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
        return addInfo;
    }
    public Map getAllBannerHasDomain(String date)
    {
        Map<String,String> mp=new HashMap<String, String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = "+ date +" ORDER BY bannerid DESC");
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_all_site"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return mp;
    }

    public List getClickAndDomainByBannerid(String bannerid ,String date)
    {
        List CAD = new ArrayList<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT domain,totalclick FROM "+V3_DOMAIN_DATE+" WHERE bannerid = "+ bannerid+" AND dt = '"+date+"'");            
            while (rs.next())
            {
                CAD.add(rs.getString("domain")+"_"+rs.getString("totalclick"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return CAD;
    }

    public int getFraudClickByDomainAndBanner(String bannerid, String domain,String date)
    {
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = '"+bannerid+"' AND domain = '"+domain+"' AND created_date = '"+ date +"' AND status = 0");

            while (rs.next())
            {
                retValue = rs.getInt(1);
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return retValue;
    }

    public Map getAllFraudClickByDay(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,domain,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,domain,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("domain")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByZoneDay(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,zoneid,campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,zoneid,campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("zoneid")+"_"+rs.getString("campaingnid")+"_"+rs.getString("bannerid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByZone(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,zoneid,campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,zoneid,campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("campaingnid")+"_"+rs.getString("zoneid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByBannerid(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByCampaignid(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("campaingnid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return fraudclicks;
    }

    public Map getMoneyByBannerId(String date)
    {
        Map<String, Integer> MoneyByBanner = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,SUM(money) FROM "+ TEST_MOB_DOMAIN_DATE +" WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                MoneyByBanner.put(rs.getString("bannerid"), rs.getInt("SUM(money)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return MoneyByBanner;
    }
    public Map getMoneyByCampaignIdAndDomain(String date)
    {
            Map<String, String> MoneyByBanner = new HashMap<>();

            try
            {
                Statement st= (Statement)conn.createStatement();
                ResultSet rs = st.executeQuery("SELECT d.campaignid, d.domain, sum(realclick), sum(money), sum(totalview) FROM v3_domain_date d WHERE dt = '"+ date +"' GROUP BY d.campaignid ,d.domain");
                //Debug.println("SELECT d.campaignid, d.domain, sum(totalclick), sum(money) FROM v3_domain_date d WHERE dt = '"+ date +"' AND totalclick > 0 GROUP BY d.campaignid ,d.domain");
                while (rs.next())
                {
                    MoneyByBanner.put(rs.getString("d.campaignid")+"_"+rs.getString("d.domain"),rs.getString("sum(money)")+"_"+rs.getString("sum(realclick)")+"_"+rs.getString("sum(totalview)"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 23.86.");
            }
            return MoneyByBanner;
    }

    public Map getClickByDomainDate(String date)
    {
         Map<String, Long> ClickByDomainDate = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,domain,totalclick FROM v3_domain_date WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                if(rs.getLong("totalclick") >0)
                {
                    ClickByDomainDate.put(rs.getString("bannerid")+"_"+rs.getString("domain")+"_"+date, rs.getLong("totalclick"));
                }
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return ClickByDomainDate;
    }
   

    public void insertBalanceAndPromotionMoney(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ BAN_PRO_TABLE +"(userid,campaignid,domain,totalclick,totalview,balance,promotions,groupid,dt) values " + value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }

    public Map getSimilityByID(long NewsID)
    {
        Map<Long, String> map = new HashMap<>();
        try
        {
                Statement s = conn.createStatement();
                String sql = "SELECT uid,GROUP_CONCAT(cmp) FROM ( SELECT DISTINCT C.campaignid AS cmp, A.uid AS uid Where    GROUP BY uid";
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("uid"),rs.getString("GROUP_CONCAT(cmp)"));
                }
                rs.close();
                s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return map;
    }

    public void getNewsIDbyCat(int CatID,int limit,List newsList)
    {        
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT NewsId FROM NewsFeaturedRecomment WHERE GeneralCatID = "+ CatID +" ORDER BY id desc limit "+limit;
            //Debug.println(sql);
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                newsList.add(rs.getLong("NewsId"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
    }

    public void getAllsimilityByNewsID(long NewsID,List infor)
    {
        //List infor = new ArrayList();
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT second FROM bigdata_pair_simi where first = "+NewsID;
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getLong("second"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }        
    }
    

    public Map getRankChangeByTime(int cat,int limit)
    {
         Map<Long, Double> map = new HashMap<>();
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT NewsId,Rank FROM NewsScoreRecomment WHERE DATE(CreatedDate) = CURDATE() and GeneralCatID = "+cat+" order by CreatedDate desc limit "+limit;
                Debug.println(sql);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("NewsId"),rs.getDouble("Rank"));
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 23.86.");
            }
         return map;
    }

    public boolean checkVietNamNet(long NewsId)
    {
            boolean retVal = false;
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT * FROM ViewForAdmicro WHERE NewsUrl LIKE '%vietnamnet%' AND NewsID = "+NewsId;
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    retVal = true;
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 23.86.");
            }

            return retVal;
    }

    public void insertDataCampainHourForCPC(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_TEST +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }

    public Map getAllBannerRunning(String date)
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT bannerid ,GROUP_CONCAT(domain) AS list_site,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview FROM v3_domain_date WHERE dt ='"+date+"' GROUP BY bannerid ");
            //Debug.println("SELECT bannerid ,GROUP_CONCAT(domain) AS domain,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview FROM v3_domain_date WHERE dt ='"+date+"' GROUP BY bannerid");
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_site")+"_"+rs.getString("totalclick")+"_"+rs.getString("totalview")+"_"+date);
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return mp;
    }

    public String getDetailBannerByDomain(String bannerid ,String date,String domainSlice)
    {
        String retValue = "";
        try
        {
            Statement st= (Statement)conn.createStatement();           
            ResultSet rs = st.executeQuery("SELECT bannerid ,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview "
                                                + "FROM v3_domain_date WHERE dt ='"+date+"' AND bannerid = '"+bannerid+"' "
                                                + "AND domain IN ("+domainSlice+") GROUP BY bannerid ");
            
            /*Debug.println("SELECT bannerid ,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview "
                                                + "FROM v3_domain_date WHERE dt ='"+date+"' AND bannerid = '"+bannerid+"' "
                                                + "AND domain IN ("+domainSlice+") GROUP BY bannerid ");*/

            while (rs.next())
            {
               retValue =  rs.getString("totalclick")+"_"+rs.getString("totalview");
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return retValue;
    }

    public Map getClickByDomainDateCPCandCPCplus(String date)
    {
        Map<String, String> ClickByDomainDate = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE+" WHERE dt = '"+date+"' GROUP BY bannerid,dt");
            //Debug.println("SELECT campaignid,bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE_VMASK+" WHERE dt = '"+date+"' GROUP BY campaignid ,bannerid,dt");
            while (rs.next())
            {
                ClickByDomainDate.put(rs.getString("bannerid")+"_"+date, rs.getLong("totalclick")+"_"+rs.getLong("totalview")+"_"+rs.getLong("money"));
            }

            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
        return ClickByDomainDate;
    }

    public Map getAllCampaignByDate(String date)
    {
        Map<String, String> allCampaignid = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,SUM(totalview) AS totalview,SUM(totalclick) AS totalclick,SUM(realclick) AS realclick,SUM(clickcharge) AS clickcharge,SUM(money) AS money,dt FROM v3_ban_date where dt ='"+ date +"' GROUP BY campaignid,dt");
            while (rs.next())
            {
                allCampaignid.put(rs.getString("campaignid")+"_"+rs.getString("dt"), rs.getLong("totalview")+"_"+rs.getLong("totalclick")+"_"+rs.getLong("realclick")+"_"+rs.getLong("clickcharge")+"_"+rs.getLong("money"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

        return allCampaignid;
    }

    public void insertDataCampainTemp(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into v3_cam_date (campaignid,totalclick,totalview,realclick,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            Debug.println(sql);

            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }

    public void insertDataZoneCPC(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE +"(campaignid, zoneid, bannerid, totalclick, realclick,clickcharge,totalview, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }

    }
    //-------------------------start mobile-----------------------------------
    public void loadToV3DomainDateFromFile(String filePath) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;

        try {            
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+TEST_MOB_DOMAIN_DATE+" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, bannerid,domain," +
                " totalclick,realclick,totalview,viewcharge, @domaincrcRaw," +
                " domaingroup,@domaingroupRaw,@dtStr,preoder,money)" +
                " SET domaingroupcrc=crc32(@domaingroupRaw), dt=date(@dtStr)," +
                " domaincrc=crc32(@domaincrcRaw)";
            Debug.println(query);
            s = conn.createStatement();            
            resultSet = s.executeQuery(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataDomainByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;

        try {            
            String query = "DELETE FROM "+TEST_MOB_DOMAIN_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataBannerByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+TEST_MOB_BAN_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataCampaignByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+TEST_MOB_CAM_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataCampaignHourlyByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+TEST_MOB_CAM_HOURLY+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataBannerHourlyByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+TEST_MOB_BAN_HOURLY+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public Map getClickByDomainDateMobile(String date)
    {
        Map<String, String> ClickByDomainDate = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+TEST_MOB_DOMAIN_DATE+" WHERE dt = '"+date+"' GROUP BY bannerid,dt");
            //Debug.println("SELECT campaignid,bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE_VMASK+" WHERE dt = '"+date+"' GROUP BY campaignid ,bannerid,dt");
            while (rs.next())
            {
                ClickByDomainDate.put(rs.getString("bannerid")+"_"+date, rs.getLong("totalclick")+"_"+rs.getLong("totalview")+"_"+rs.getLong("money"));
            }

            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return ClickByDomainDate;
    }
    
    public Map getClickByCampaignid(String date)
    {
        Map<String, String> ClickAndMoneyByCamp = new HashMap<>();
         
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT campaignid,SUM(realclick) AS sumrealclick,SUM(viewcharge) AS sumviewcharge,SUM(money) AS money FROM "+ TEST_MOB_DOMAIN_DATE +" WHERE dt = '"+ date +"' GROUP BY campaignid,dt");

            while (rs.next())
            {
                //if(rs.getLong("sumrealclick") > 0)
                //{
                    ClickAndMoneyByCamp.put(rs.getString("campaignid")+"_"+date, rs.getLong("sumrealclick")+"_"+rs.getLong("sumviewcharge")+"_"+rs.getLong("money"));
                //}
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return ClickAndMoneyByCamp;
    }
    
    public void loadToV3BanDateFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ TEST_MOB_BAN_DATE +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, bannerid," +
                "totalclick,realclick,clickcharge,totalview,viewcharge,uc," +
                "uv,MB,MT,MN,Other,dt,money)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            //conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public Map getAllCampaignByDateMobile(String date)
    {
        Map<String, String> allCampaignid = new HashMap<>();        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,SUM(totalview) AS totalview,SUM(totalclick) AS totalclick,SUM(realclick) AS realclick,SUM(clickcharge) AS clickcharge,SUM(money) AS money,dt FROM "+ V3_BAN_DATE +" where dt ='"+ date +"' GROUP BY campaignid,dt");
            
            Debug.println("SELECT campaignid,SUM(totalview) AS totalview,SUM(totalclick) AS totalclick,SUM(realclick) AS realclick,SUM(clickcharge) AS clickcharge,SUM(money) AS money,dt FROM "+ V3_BAN_DATE +" where dt ='"+ date +"' GROUP BY campaignid,dt");
            
            while (rs.next())
            {
                allCampaignid.put(rs.getString("campaignid")+"_"+rs.getString("dt"), rs.getLong("totalview")+"_"+rs.getLong("totalclick")+"_"+rs.getLong("realclick")+"_"+rs.getLong("clickcharge")+"_"+rs.getLong("money"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return allCampaignid;
    }
    
    public void loadToV3CamDateFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ TEST_MOB_CAM_DATE +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, totalclick," +
                "totalview,realclick,clickcharge,viewcharge,uv,uc," +
                "dt,ctr,moneyc,moneyv)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            //conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void loadToV3CamHourlyFromFile(String filePath) throws Exception {
        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+TEST_MOB_CAM_HOURLY+" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, h," +
                "clicks,views," +
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            //conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void loadToV3BannerHourlyFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ TEST_MOB_BAN_HOURLY +" FIELDS TERMINATED BY ';' " +
                "LINES TERMINATED BY ';\n' (bannerid, clickhour," +
                "viewhour," +
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            //conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public Map getCamMobileByCPM(String date)
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus           
            String sql ="SELECT A.`campaignid`,SUM(money) as money  "
                    + "FROM "+TEST_MOB_BAN_DATE+" B  "
                    + "INNER JOIN `ox_banners` A ON A.`bannerid` = B.`bannerid`  "
                    + "WHERE A.bannertype =2 AND B.dt = '"+date+"' "                                      
                    + "GROUP BY A.`campaignid` ";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("campaignid"),rs.getString("money"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
    public Map getCamMobileByCPC(String date)
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
           
            String sql ="SELECT A.`campaignid`,SUM(money) as money  "
                    + "FROM "+TEST_MOB_BAN_DATE+" B  "
                    + "INNER JOIN `ox_banners` A ON A.`bannerid` = B.`bannerid`  "
                    + "WHERE A.bannertype = 1 AND B.dt = '"+date+"' "                                      
                    + "GROUP BY A.`campaignid` ";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("campaignid"), rs.getString("money"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
    public Map getCamMobile(String date)
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus           
            String sql ="SELECT A.`campaignid`,SUM(B.totalclick) AS totalclick,SUM(B.totalview) as totalview,SUM(B.realclick) as realclick,SUM(B.clickcharge) as clickcharge,SUM(money) as money  "
                    + "FROM "+TEST_MOB_BAN_DATE+" B  "
                    + "INNER JOIN `ox_banners` A ON A.`bannerid` = B.`bannerid`  "
                    + "WHERE B.dt = '"+date+"' "                                      
                    + "GROUP BY A.`campaignid` ";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("campaignid")+"_"+ date, rs.getString("totalclick")+"_"+rs.getString("totalview")+"_"+rs.getString("realclick")+"_"+rs.getString("clickcharge")+"_"+rs.getString("money"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
    public Map getBannerTypeDataMobile()
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
           
            String sql ="SELECT DISTINCT (A.`bannerid`),A.bannertype AS bannertype "
                    + "FROM `ox_banners` A  "
                    + "INNER JOIN `ox_campaigns` B ON A.`campaignid` = B.`campaignid` "                                    
                    + "WHERE B.`revenue_type`=11 AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "                    
                    + "ORDER BY A.is_smallbiz ASC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"),rs.getString("bannertype"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mp;
    }
    
}
